package com.example.dps;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;


public class TransferDBHelper extends SQLiteOpenHelper{
	
	static final String tag = "TransferDBHelper";
	
	/**********************************************************************
	 * Database Variables
	 */
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "DownPaymentSaver.db";
    /********************************************************************/
    
    
	/**********************************************************************
     * Accounts Table Variables
     */
    public static final String TRANSFER_TABLE_NAME = "Transfers";
    public static final String COLUMN_TRANSFER_ID = "_id";
    public static final String COLUMN_TRANSFER_AMOUNT = "amount";
    public static final String COLUMN_TRANSFER_FREQUENCY = "frequency";
    public static final String COLUMN_TRANSFER_ENABLED = "enabled";
    public static final String COLUMN_TRANSFER_FROM = "from_account";
    public static final String COLUMN_TRANSFER_TO = "to_account";
    public static final String COLUMN_TRANSFER_STARTING = "starting";
    public static final String COLUMN_TRANSFER_ENDING = "ending";
    public static final String COLUMN_TRANSFER_LAST_AUTO_TRANSFER = "last_transfer";
    
    
    
    public static final String CREATE_TRANSFERS_TABLE = "CREATE TABLE IF NOT EXISTS " + TRANSFER_TABLE_NAME +
    		" (" + 
    		COLUMN_TRANSFER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "+ 
    		COLUMN_TRANSFER_AMOUNT+" INTEGER, " +
    		COLUMN_TRANSFER_FREQUENCY + " VARCHAR(180)," + //TODO what format?
    		COLUMN_TRANSFER_STARTING + " TEXT," +
    		COLUMN_TRANSFER_ENDING + " TEXT," + 
    		COLUMN_TRANSFER_LAST_AUTO_TRANSFER + " TEXT," +
    		COLUMN_TRANSFER_ENABLED + " INTEGER, " +
    		COLUMN_TRANSFER_FROM + " VARCHAR(80)," +
    		COLUMN_TRANSFER_TO + " VARCHAR(80)," +
    		"FOREIGN KEY (" + COLUMN_TRANSFER_FROM + ") REFERENCES " +	AccountDBHelper.ACCOUNT_TABLE_NAME + "(" + AccountDBHelper.COLUMN_ACCOUNT_NAME + "),"	+  //TODO how to store FROM/TO accounts? checking num? reference to ACCOUNTS?
    		"FOREIGN KEY (" + COLUMN_TRANSFER_TO + ") REFERENCES " +	AccountDBHelper.ACCOUNT_TABLE_NAME + "(" + AccountDBHelper.COLUMN_ACCOUNT_NAME + ")"	+ 
    		");";
	private SQLiteDatabase db;
    /** End Account Table Variables *************************************/

	public TransferDBHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	
	public TransferDBHelper(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
		this.db = getWritableDatabase();
		
		//db.execSQL("DROP TABLE Transfers");
		
		
		db.execSQL(CREATE_TRANSFERS_TABLE);
		
		this.db.close();
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public Cursor getTransfersAsCursor(){
		
		
		String query = "SELECT * FROM " + TRANSFER_TABLE_NAME + ";";
		
		this.db = getReadableDatabase();
		
		Cursor transfersCursor = db.rawQuery(query, null);
		
		
		return transfersCursor;
		
	}
	
	public Transfer[] getTransfersAsTransfer(){
		
		
		String query = "SELECT * FROM " + TRANSFER_TABLE_NAME + ";";
		
		this.db = getReadableDatabase();
		
		Cursor transfersCursor = db.rawQuery(query, null);
		
		
		Transfer[] transfers = new Transfer[transfersCursor.getCount()];
		
		for(int i = 0; i < transfersCursor.getCount(); i++){
			transfersCursor.moveToPosition(i);
			try {
				transfers[i] = new Transfer(transfersCursor);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		transfersCursor.close();
		this.db.close();
		
		return transfers;
		
	}
	
	
	/**
	 * Adds a transfer to the transfer database
	 * @param amount the amount to transfer
	 * @param from id of the account to take the money from 
	 * @param to id of the account to put the money into
	 * @param frequency how often the transfer should occur (daily, weekly, etc)
	 * @param starting when the transfer should start (today, or another date )
	 */
	public void addTransfer(int amount, String from, String to, String frequency, String starting, String ending){
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_TRANSFER_AMOUNT, amount);
		values.put(COLUMN_TRANSFER_FROM, from);
		values.put(COLUMN_TRANSFER_TO, to);
		values.put(COLUMN_TRANSFER_FREQUENCY, frequency);
		values.put(COLUMN_TRANSFER_STARTING, starting);
		values.put(COLUMN_TRANSFER_LAST_AUTO_TRANSFER, "");
		values.put(COLUMN_TRANSFER_ENDING, ending);
		values.put(COLUMN_TRANSFER_ENABLED, 1);
		
		this.db = getWritableDatabase();
		
		Log.e(tag, "ADDING TRANSFER");
		
		int result = (int) db.insert(TRANSFER_TABLE_NAME, null, values);
		
		Log.e(tag, "insert result = " + result);
		
		
	}

	/**
	 * returns a cursor containing the transfer with the given ID
	 * @param transferID
	 * @return
	 */
	public Transfer getTransferFromID(int transferID) {
		
		Log.e(tag, "getTransferFrom ID: id === " + transferID);
		
		String query = "SELECT * FROM " + TRANSFER_TABLE_NAME + " WHERE _id = " + transferID + ";";
		
		this.db = getReadableDatabase();
		
		Cursor transferCursor = db.rawQuery(query, null);
		
		if(!transferCursor.moveToFirst()){
			
			Log.e(tag, "transfer DNE!!!!! (transferFromID = " + transferID);
			transferCursor.close();
			this.db.close();
			
			return null;
		}
		else{
			Log.e(tag, "transfer exists!!!!! (transferFromID = " + transferID);
			Transfer transfer = null;
			try {
				transfer = new Transfer(transferCursor);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			transferCursor.close();
			this.db.close();
			
			return transfer;
			
		}
		
		
		
	}
	
	
	/**
	 * returns a cursor containing the transfer with the given ID
	 * @param transferID
	 * @return
	 */
	public Cursor getTransferFromIDAsCursor(int transferID) {
		
		String query = "SELECT * FROM " + TRANSFER_TABLE_NAME + " WHERE _id = " + transferID + ";";
		
		this.db = getReadableDatabase();
		
		Cursor transferCursor = db.rawQuery(query, null);
		
		transferCursor.moveToFirst();
		
		return transferCursor;
		
	}
	
	
public Date getTransferEnding(int transferID){
		
		this.db = getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT " + COLUMN_TRANSFER_ENDING + " FROM " + TRANSFER_TABLE_NAME + " WHERE " + COLUMN_TRANSFER_ID + " = " + transferID, null);
		
		String dateString = null;
		Date date = null;
		
		if(cursor.moveToFirst()){
			dateString = cursor.getString(0);
			
			//if date length = 7, then add a 0 to get the format correct. ie 2/09/1990 --> 02091990
			if(dateString.length() == 7) dateString = "0".concat(dateString);
			
			try {
				date = new SimpleDateFormat("MMddyyyy").parse(dateString);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			Log.e(tag, "transfer starting date = " +date.toString());
			
			cursor.close();
			this.db.close();
			
			return date;
			
		}
		
		Log.e("date = ", date.toString());
		
		cursor.close();
		this.db.close();
		
		return null;
		
	}

	public Date getTransferStarting(int transferID){
		
		this.db = getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT " + COLUMN_TRANSFER_STARTING + " FROM " + this.TRANSFER_TABLE_NAME + " WHERE " + COLUMN_TRANSFER_ID + " = " + transferID, null);
		
		String dateString = null;
		Date date = null;
		
		if(cursor.moveToFirst()){
			dateString = cursor.getString(0);
			
			//if date length = 7, then add a 0 to get the format correct. ie 2/09/1990 --> 02091990
			if(dateString.length() == 7) dateString = "0".concat(dateString);
			
			try {
				date = new SimpleDateFormat("MMddyyyy").parse(dateString);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			Log.e(tag, "transfer starting date = " +date.toString());
			
			this.db.close();
			
			return date;
			
		}
		
		Log.e("date = ", date.toString());
		
		this.db.close();
		
		return null;
		
	}

	
	/** updates a transfer with the given values using the TID **/
	public void updateTransfer(int tid, int amount, String from, String to,
			String frequency) {
		
		this.db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_TRANSFER_FROM, from);
		values.put(COLUMN_TRANSFER_TO, to);
		values.put(COLUMN_TRANSFER_AMOUNT, amount);
		values.put(COLUMN_TRANSFER_FREQUENCY, frequency);
		
		String where = "_id=" + tid;

		db.update(TRANSFER_TABLE_NAME, values, where, null);
		
	}

	/**
	 * deletes a transfer with the given Transfer ID
	 * @param tid the Transfer ID
	 */
	public void delete(int tid) {
		
		 this.db = getWritableDatabase();
		
		String whereClause = "_id = ?";
		String[] whereArgs = new String[] { String.valueOf(tid)};
		int result = db.delete(TRANSFER_TABLE_NAME, whereClause, whereArgs);
		
		if(result == 1) Log.e("deleting", "DELETED RESULT!****");
		
	}
	
	public void setEnabled(int tid, int enabled){
		
		this.db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_TRANSFER_ENABLED, enabled);
		
		String where = "_id=" + tid;

		db.update(TRANSFER_TABLE_NAME, values, where, null);
		
	}
	
	public int getEnabled(int tid){
		
		this.db = getReadableDatabase();
		
		String query = "SELECT enabled FROM Transfers WHERE _id = " + tid;
		
		Cursor cursor = db.rawQuery(query, null);
		
		if(cursor.moveToFirst()){
			return cursor.getInt(cursor.getColumnIndex(COLUMN_TRANSFER_ENABLED));
		}
		else return -1;
		
	}
	
	
	/**
	 * gets the next occuring scheduled transfer 
	 * @return
	 * @throws ParseException
	 */
	public Transfer getNextTransfer() throws ParseException{
		
		String query = "SELECT * FROM " + TRANSFER_TABLE_NAME + ";";
		
		this.db = getReadableDatabase();
		
		Cursor results = this.db.rawQuery(query, null);
		
		Transfer[] transfers = new Transfer[results.getCount()];
		
		Transfer nextTransfer = null;
		
		/** get all the transfers **/
		for(int i = 0; i < results.getCount(); i++){
			
			results.moveToPosition(i);
			transfers[i] = new Transfer(results);
			
		}
		
		
		/** select the transfer with the earliest "next" date **/
		for(int i = 0; i < transfers.length; i++){
			
			if(nextTransfer == null){
				
				if(transfers[i].lastTransfer == null){
					/** the next transfer = the starting date **/
					transfers[i].lastTransfer = transfers[i].starting;
				}

				
				nextTransfer = transfers[i];
			}
			else{
				
				/** next transfer == lastTransfer + frequency **/
				Date currentNextTransferDate = nextTransfer.lastTransfer;
				
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(currentNextTransferDate);
				calendar.add(Calendar.DAY_OF_YEAR, nextTransfer.interval);
				
				currentNextTransferDate = calendar.getTime();
				
				Log.e(tag, "currentNextTransferDate = " + currentNextTransferDate.toLocaleString());
				
				Date iNextTransferDate = null;
				
				/** if the transfer did not already have a deposit **/
				if(transfers[i].lastTransfer == null){
					/** the next transfer = the starting date **/
					iNextTransferDate = transfers[i].starting;
				}
				else{
					/** else next transfer = last transfer + interval **/
					iNextTransferDate = transfers[i].lastTransfer;
					calendar.setTime(iNextTransferDate);
					calendar.add(Calendar.DAY_OF_YEAR, transfers[i].interval);
					iNextTransferDate = calendar.getTime();
				}
				
				
				Log.e(tag, "iNextTransferDate = " + iNextTransferDate.toLocaleString());
				
				/** if transfer[i] date is < nextTransfer, update nextTransfer **/
				if(currentNextTransferDate.getTime() > iNextTransferDate.getTime()){
					nextTransfer = transfers[i];
				}

			}
			
		}

		results.close();
		this.db.close();
		
		return nextTransfer;
		
		
		
	}

	public void updateTransfer(Transfer transfer) {
		
		ContentValues values = new ContentValues();
		
		/** convert bool to int **/
		int enabled = 0;
		if(transfer.enabled) enabled = 1;
		
		values.put(COLUMN_TRANSFER_FROM, transfer.from);
		values.put(COLUMN_TRANSFER_TO, transfer.to);
		
		values.put(COLUMN_TRANSFER_AMOUNT, transfer.amount);
		values.put(COLUMN_TRANSFER_ID, transfer.id);
		
		values.put(COLUMN_TRANSFER_ENABLED, enabled);
		
		values.put(COLUMN_TRANSFER_ENDING, new SimpleDateFormat("MM/dd/yyyy").format(transfer.ending));
		values.put(COLUMN_TRANSFER_STARTING, new SimpleDateFormat("MM/dd/yyyy").format(transfer.starting));
		
		
		values.put(COLUMN_TRANSFER_LAST_AUTO_TRANSFER, new SimpleDateFormat("MM/dd/yyyy").format(transfer.lastTransfer));
		
		values.put(COLUMN_TRANSFER_FREQUENCY, transfer.frequency);
		
		this.db = getWritableDatabase();
		
		/** update object where _id = transfer.id **/
		Log.e(tag, "**** update transfer ID = " + transfer.id);
		Log.e(tag, "****updating transfer with new LastTransfer ===" + transfer.lastTransfer.toLocaleString());
		Log.e(tag, "****updating transfer with new nextTransfer ===" + transfer.nextTransfer.toLocaleString());
		
		this.db.update(TRANSFER_TABLE_NAME, values, "_id = " + transfer.id, null);
		
		Transfer test = this.getTransferFromID(transfer.id);
		
		if(test.id == transfer.id) Log.e(tag,"test == transfer: SUCESS");
		else Log.e(tag, "test!=transfer: FAIL *********");
		
		
		this.db.close();
		
		
	}

}
